<?php

namespace Database;

class QueryBuilder {
    protected $connection;
    protected $table;
    protected $columns = ['*'];
    protected $wheres = [];
    protected $orders = [];
    protected $limit;
    protected $offset;
    protected $joins = [];
    protected $groups = [];
    protected $havings = [];
    
    /**
     * 构造函数
     */
    public function __construct(Connection $connection) {
        $this->connection = $connection;
    }
    
    /**
     * 设置查询的表名
     */
    public function table($table) {
        $this->table = $table;
        return $this;
    }
    
    /**
     * 设置查询的列
     */
    public function select($columns = ['*']) {
        $this->columns = is_array($columns) ? $columns : func_get_args();
        return $this;
    }
    
    /**
     * 添加where条件
     */
    public function where($column, $operator = null, $value = null, $boolean = 'AND') {
        // 处理闭包条件
        if ($column instanceof \Closure) {
            $query = new static($this->connection);
            $column($query);
            
            $this->wheres[] = [
                'type' => 'nested',
                'query' => $query,
                'boolean' => $boolean
            ];
            
            return $this;
        }
        
        // 处理数组条件
        if (is_array($column)) {
            foreach ($column as $key => $value) {
                $this->where($key, '=', $value, $boolean);
            }
            
            return $this;
        }
        
        // 如果只有两个参数，调整参数位置
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->wheres[] = [
            'type' => 'basic',
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'boolean' => $boolean
        ];
        
        return $this;
    }
    
    /**
     * 添加OR WHERE条件
     */
    public function orWhere($column, $operator = null, $value = null) {
        return $this->where($column, $operator, $value, 'OR');
    }
    
    /**
     * 添加WHERE IN条件
     */
    public function whereIn($column, array $values, $boolean = 'AND') {
        $this->wheres[] = [
            'type' => 'in',
            'column' => $column,
            'values' => $values,
            'boolean' => $boolean
        ];
        
        return $this;
    }
    
    /**
     * 添加ORDER BY子句
     */
    public function orderBy($column, $direction = 'ASC') {
        $this->orders[] = [
            'column' => $column,
            'direction' => strtoupper($direction) === 'ASC' ? 'ASC' : 'DESC'
        ];
        
        return $this;
    }
    
    /**
     * 设置LIMIT子句
     */
    public function limit($limit) {
        $this->limit = $limit;
        return $this;
    }
    
    /**
     * 设置OFFSET子句
     */
    public function offset($offset) {
        $this->offset = $offset;
        return $this;
    }
    
    /**
     * 添加JOIN子句
     */
    public function join($table, $first, $operator = null, $second = null, $type = 'INNER') {
        $join = ['table' => $table, 'type' => $type];
        
        if ($first instanceof \Closure) {
            $join['on'] = $first;
        } else {
            $join['first'] = $first;
            $join['operator'] = $operator;
            $join['second'] = $second;
        }
        
        $this->joins[] = $join;
        
        return $this;
    }
    
    /**
     * 添加LEFT JOIN子句
     */
    public function leftJoin($table, $first, $operator = null, $second = null) {
        return $this->join($table, $first, $operator, $second, 'LEFT');
    }
    
    /**
     * 添加GROUP BY子句
     */
    public function groupBy(...$groups) {
        $this->groups = array_merge($this->groups, $groups);
        return $this;
    }
    
    /**
     * 添加HAVING子句
     */
    public function having($column, $operator = null, $value = null, $boolean = 'AND') {
        $this->havings[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'boolean' => $boolean
        ];
        
        return $this;
    }
    
    /**
     * 获取单条记录
     */
    public function first() {
        $this->limit(1);
        $results = $this->get();
        return !empty($results) ? $results[0] : null;
    }
    
    /**
     * 获取所有记录
     */
    public function get() {
        $sql = $this->toSql();
        $bindings = $this->getBindings();
        
        $statement = $this->connection->query($sql, $bindings);
        return $statement->fetchAll();
    }
    
    /**
     * 插入记录
     */
    public function insert(array $values) {
        if (empty($values)) {
            return true;
        }
        
        // 处理批量插入
        if (!isset($values[0])) {
            $values = [$values];
        }
        
        $columns = array_keys($values[0]);
        
        $sql = sprintf(
            'INSERT INTO %s (%s) VALUES %s',
            $this->table,
            implode(', ', $columns),
            implode(', ', array_fill(0, count($values), '(' . implode(', ', array_fill(0, count($columns), '?')) . ')'))
        );
        
        $bindings = [];
        foreach ($values as $record) {
            foreach ($columns as $column) {
                $bindings[] = $record[$column];
            }
        }
        
        $this->connection->query($sql, $bindings);
        return $this->connection->lastInsertId();
    }
    
    /**
     * 更新记录
     */
    public function update(array $values) {
        $sets = [];
        $bindings = [];
        
        foreach ($values as $column => $value) {
            $sets[] = "{$column} = ?";
            $bindings[] = $value;
        }
        
        // 添加where条件的绑定值
        $bindings = array_merge($bindings, $this->getWhereBindings());
        
        $sql = sprintf(
            'UPDATE %s SET %s %s',
            $this->table,
            implode(', ', $sets),
            $this->compileWheres()
        );
        
        $this->connection->query($sql, $bindings);
        return true;
    }
    
    /**
     * 删除记录
     */
    public function delete() {
        $sql = sprintf('DELETE FROM %s %s', $this->table, $this->compileWheres());
        $bindings = $this->getWhereBindings();
        
        $this->connection->query($sql, $bindings);
        return true;
    }
    
    /**
     * 生成完整的SQL语句
     */
    public function toSql() {
        $sql = sprintf(
            'SELECT %s FROM %s %s %s %s %s %s %s',
            $this->compileColumns(),
            $this->table,
            $this->compileJoins(),
            $this->compileWheres(),
            $this->compileGroups(),
            $this->compileHavings(),
            $this->compileOrders(),
            $this->compileLimitOffset()
        );
        
        return trim(preg_replace('/\s+/', ' ', $sql));
    }
    
    /**
     * 获取所有绑定参数
     */
    protected function getBindings() {
        $bindings = [];
        
        // 添加where条件的绑定值
        $bindings = array_merge($bindings, $this->getWhereBindings());
        
        // 添加having条件的绑定值
        foreach ($this->havings as $having) {
            $bindings[] = $having['value'];
        }
        
        return $bindings;
    }
    
    /**
     * 获取where条件的绑定参数
     */
    protected function getWhereBindings() {
        $bindings = [];
        
        foreach ($this->wheres as $where) {
            if ($where['type'] === 'basic') {
                $bindings[] = $where['value'];
            } elseif ($where['type'] === 'in') {
                $bindings = array_merge($bindings, $where['values']);
            } elseif ($where['type'] === 'nested') {
                $bindings = array_merge($bindings, $where['query']->getWhereBindings());
            }
        }
        
        return $bindings;
    }
    
    /**
     * 编译查询列
     */
    protected function compileColumns() {
        return implode(', ', $this->columns);
    }
    
    /**
     * 编译JOIN子句
     */
    protected function compileJoins() {
        if (empty($this->joins)) {
            return '';
        }
        
        $sql = [];
        
        foreach ($this->joins as $join) {
            $table = $join['table'];
            $type = $join['type'];
            
            if (isset($join['on'])) {
                // 处理闭包形式的JOIN
                $query = new static($this->connection);
                $join['on']($query);
                $on = substr($query->compileWheres(), 5); // 去掉前面的WHERE
            } else {
                // 处理基本形式的JOIN
                $on = sprintf('%s %s %s', $join['first'], $join['operator'], $join['second']);
            }
            
            $sql[] = sprintf('%s JOIN %s ON %s', $type, $table, $on);
        }
        
        return implode(' ', $sql);
    }
    
    /**
     * 编译WHERE子句
     */
    protected function compileWheres() {
        if (empty($this->wheres)) {
            return '';
        }
        
        $sql = [];
        
        foreach ($this->wheres as $i => $where) {
            $boolean = $i === 0 ? 'WHERE' : $where['boolean'];
            
            if ($where['type'] === 'basic') {
                $sql[] = sprintf('%s %s %s ?', $boolean, $where['column'], $where['operator']);
            } elseif ($where['type'] === 'in') {
                $placeholders = implode(', ', array_fill(0, count($where['values']), '?'));
                $sql[] = sprintf('%s %s IN (%s)', $boolean, $where['column'], $placeholders);
            } elseif ($where['type'] === 'nested') {
                $nested = $where['query']->compileWheres();
                if (!empty($nested)) {
                    $nested = substr($nested, 5); // 去掉前面的WHERE
                    $sql[] = sprintf('%s (%s)', $boolean, $nested);
                }
            }
        }
        
        return implode(' ', $sql);
    }
    
    /**
     * 编译GROUP BY子句
     */
    protected function compileGroups() {
        if (empty($this->groups)) {
            return '';
        }
        
        return 'GROUP BY ' . implode(', ', $this->groups);
    }
    
    /**
     * 编译HAVING子句
     */
    protected function compileHavings() {
        if (empty($this->havings)) {
            return '';
        }
        
        $sql = [];
        
        foreach ($this->havings as $i => $having) {
            $boolean = $i === 0 ? 'HAVING' : $having['boolean'];
            $sql[] = sprintf('%s %s %s ?', $boolean, $having['column'], $having['operator']);
        }
        
        return implode(' ', $sql);
    }
    
    /**
     * 编译ORDER BY子句
     */
    protected function compileOrders() {
        if (empty($this->orders)) {
            return '';
        }
        
        $sql = [];
        
        foreach ($this->orders as $order) {
            $sql[] = sprintf('%s %s', $order['column'], $order['direction']);
        }
        
        return 'ORDER BY ' . implode(', ', $sql);
    }
    
    /**
     * 编译LIMIT和OFFSET子句
     */
    protected function compileLimitOffset() {
        $sql = '';
        
        if ($this->limit !== null) {
            $sql .= sprintf('LIMIT %d', $this->limit);
        }
        
        if ($this->offset !== null) {
            $sql .= sprintf(' OFFSET %d', $this->offset);
        }
        
        return $sql;
    }
}